package NamedParameterJdbcTemplate;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Mains {

    private static Person person;

    private static String query="SELECT email FROM spitter WHERE id_identity=13";

    private static String insert="INSERT INTO spitter (username,PASSWORD,first_name,last_name,email)" +
            " VALUES(:username,:password,:first_name,:last_name,:email)";

    private static String update="UPDATE spitter SET username=:username WHERE id_identity=:id_identity";

    private static String delete="DELETE FROM spitter WHERE id_identity=:id_identity";

    ApplicationContext context=new AnnotationConfigApplicationContext(Config.class);
    NamedParameterJdbcTemplate template= (NamedParameterJdbcTemplate)
            context.getBean("namedParameterJdbcTemplate");

    @Test
    public void test(){

            /*
            使用Map集合添加数据
             */
            Map<String,Object> map=new HashMap<>();

            map.put("username","胡歌3");//参数绑定指定的值
            map.put("password","root1");
            map.put("first_name","胡1");
            map.put("last_name","歌1");
            map.put("email","huangbiao11222@sina.com");

            template.update(insert,map);

            /*
            使用Map集合修改数据
             */
            person =(Person) context.getBean("getPerson");
            Map<String,Object> map1=new HashMap<>();
            map1.put("username","江骚货");//设置参数值
            map1.put("id_identity","3");//id不存在不会报错
            template.update(update,map1);



            /*
            使用BeanPropertySqlParameterSource修改数据(面向接口)
             */
            person.setUsername("黄河入海流");
            person.setId_identity(7);//设置参数值,id不存在不会报错
            SqlParameterSource source=new BeanPropertySqlParameterSource(person);
            template.update(update,source);

            /*
            使用Map集合删除数据
             */
            Map<String,Object> map2=new HashMap<>();
            map2.put("id_identity",8);
            template.update(delete,map2);

            /*
             使用BeanPropertySqlParameterSource删除数据(面向接口)
             */
            person.setId_identity(9);//id不存在不会报错
            SqlParameterSource source2=new BeanPropertySqlParameterSource(person);
            template.update(delete,source2);


    }

    @Test
    public void test2(){
        /*
        返回单格数据
         */
        //①
        String email=template.queryForObject(query,new HashMap<>(),String.class);
        Integer id_identity=template.queryForObject("SELECT id_identity FROM spitter WHERE username='江骚货'",new HashMap<>(),Integer.class);
        System.out.println("查询单行单列数据2:"+email+","+id_identity);

        //②
        String email2 = template.queryForObject(query,new EmptySqlParameterSource(),
                new SingleColumnRowMapper<>(String.class));
        System.out.println("查询单行单列数据1:"+email2);

        /*
        返回一列数据
        */
        List<Integer> list2=template.queryForList("SELECT id_identity FROM spitter WHERE first_name='胡1'",
                new HashMap<>(),Integer.class);
        list2.forEach(System.out::println);//Lambda表达式

        /*
        返回一行数据
         */
        //①
        person=template.queryForObject("SELECT * FROM spitter WHERE username='江骚货'",new HashMap<>(),
                new BeanPropertyRowMapper<Person>(Person.class));
        System.out.println("返回单行多列数据:"+person.getId_identity()+person.getEmail()+person.getFirst_name()+
                person.getLast_name()+person.getUsername()+person.getPassword());

        /*
        返回多行数据
         */
        List<Person> list1=template.query("SELECT * FROM spitter WHERE first_name='胡1'",
                new BeanPropertyRowMapper<>(Person.class));
        for (Person person:list1
             ) {
            System.out.println(person.getId_identity()+person.getEmail()+person.getFirst_name()+
                    person.getLast_name()+person.getUsername()+person.getPassword());
        }
    }
}
